import sqlite3
import sys
sys.path.append("..")
from model.Issue import Issue
state2no = {'待办的': 877565,
 '已确认': 877569,
 '技术评审中': 1244182,
 '修复中': 877573,
 '验收中': 877572,
 '已完成': 877567,
 '已取消': 877574,
 '已拒绝': 877568,
 '挂起': 1244183}

class State:
    todo = '待办的'
    confirmed = '已确认'
    technical_review = '技术评审中'
    fixing = '修复中'
    accepting = '验收中'
    completed = '已完成'
    canceled = '已取消'
    refused = '已拒绝'
    suspended = '挂起'

def create_issue(item):

    return Issue.create(item[0], item[1], item[2], item[3], item[4], item[5])




def add_issue(conn, issue):
    sql = ''' INSERT INTO issue(no, title, url, state, cve, targetId)
              VALUES(?,?,?,?,?,?) '''
    cur = conn.cursor()
    cur.execute(sql, (issue.no, issue.title, issue.url, issue.state, issue.cve, issue.targetId))
    conn.commit()

def get_issues(conn, state=None):
    cur = conn.cursor()
    if state:
        cur.execute("SELECT * FROM issue WHERE state = ?", (state,))
    else:
        cur.execute("SELECT * FROM issue")
    rows = cur.fetchall()
    return rows

def get_issue(conn, no):
    cur = conn.cursor()
    cur.execute("SELECT * FROM issue WHERE no = ?", (no,))
    rows = cur.fetchall()
    if len(rows) == 0:
        return None
    return create_issue(rows[0])

def is_exist_issue(conn, no):
    cur = conn.cursor()
    cur.execute("SELECT * FROM issue WHERE no = ?", (no,))
    rows = cur.fetchall()
    return len(rows) > 0

def delete_issue(conn, no):
    cur = conn.cursor()
    cur.execute("DELETE FROM issue WHERE no = ?", (no,))
    conn.commit()

def update_issue_status(conn, no, newState):
    cur = conn.cursor()
    cur.execute("UPDATE issue SET state = ? WHERE no = ?", (newState, no))
    conn.commit()

def getTODOIssue(conn):
    cur = conn.cursor()
    cur.execute("SELECT * FROM issue WHERE state = '待办的'")
    rows = cur.fetchall()
    return rows